﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SQLite;

namespace Ruandao.MiniAccess.SQLite3
{
    using Ruandao.MiniAccess.OrMap;
    using Ruandao.MiniAccess;
    using System.Data.Common;

    public class SQLiteAccess : IAccess
    {
        /// <summary>
        /// 数据库链接名称。
        /// </summary>
        string connectionString;

        bool isDebug = true;

        IDictionary<string, TableMap> tableMaps = new Dictionary<string, TableMap>();

        public SQLiteAccess(Assembly assembly, string providerName, string connectionString)
        {
            this.connectionString = connectionString;

            OrMapFile orm = new OrMapFile(assembly, this.AccessType, providerName);
            this.isDebug = orm.IsDebug;
            Database database = GetDatabase();

            // 遍历所有的实体类型
            foreach (EntityTag entity in orm.EntityTagList)
            {
                string classname = orm.EntityNamespace + "." + entity.Name;
                TableMap tableMap = new TableMap(entity.Table, database, CommandType.Text, isDebug, entity);
                this.tableMaps.Add(classname, tableMap);
            }

            if (orm.IsDebug) InitAccess();
        }

        protected virtual void InitAccess()
        {
            // 用来实现创建数据库对象的功能，如存储过程、视图、函数等等。
            // 默认不创建任何数据库对象。
        }

        /// <summary>
        /// 得到指定实体的OR映射数据。
        /// <br/>异常：OrMapException(指定实体的OR映射数据没有定义)。
        /// </summary>
        /// <param name="entityTypeFullName">实体的完全限定类名称。</param>
        /// <returns>实体和表的映射对象。</returns>
        TableMap GetTableMap(string entityTypeFullName)
        {
            if (tableMaps.Keys.Contains(entityTypeFullName))
                return tableMaps[entityTypeFullName];
            else
                throw new OrMapException("映射文件中没有定义实体" + entityTypeFullName + "的映射数据。");
        }


        #region IAccessProvider 成员

        public bool IsDebug
        {
            get { return this.isDebug; }
        }

        public Database GetDatabase()
        {
            return new SQLiteDatabase(connectionString);
        }

        /// <summary>
        /// 连接字符串。
        /// </summary>
        public string ConnectionString
        {
            get { return this.connectionString; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string AccessType
        {
            get { return "System.Data.SQLite"; }
        }

        /// <summary>
        /// 根据主键删除记录，可能返回0。
        /// <br/>异常：OrMapException(没有提供实体的删除命令)，SqlException(映射中指定的SQL命令不合法)
        /// </summary>
        /// <param name="keys">主键值。</param>
        /// <returns>实际删除的记录数。</returns>
        public int DeleteEntity<T>(params object[] keys)
        {
            Type entityType = typeof(T);
            TableMap tableMap = GetTableMap(typeof(T).FullName);
            if (string.IsNullOrEmpty(tableMap.DeleteCommand))
            {
                throw new OrMapException("没有提供实体的删除命令。");
            }

            using (DbCommand cmd = GetDatabase().GetSqlStringCommand(tableMap.DeleteCommand))
            {
                int j = 0;
                foreach (ColumnMap cm in tableMap.PrimaryKeyMaps)
                {
                    cmd.Parameters.Add(CreateSqliteParameter(cm.ColumnName));
                    cmd.Parameters[j].Value = keys[j];
                    j++;
                }

                return GetDatabase().ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 按条件删除记录，可能返回0。
        /// <br/>异常：SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="conditions">包含WHERE条件字符串。</param>
        /// <returns>实际删除的记录数。</returns>
        public int DeleteEntities<T>(string conditions)
        {
            TableMap tableMap = GetTableMap(typeof(T).FullName);
            return GetDatabase().ExecuteNonQuery(tableMap.DeleteByConditionsCommand + conditions);
        }

        /// <summary>
        /// 按条件删除记录，可能返回0。
        /// <br/>异常：SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="conditions">包含WHERE条件字符串。</param>
        /// <param name="parameters">对应参数列</param>
        /// <returns>实际删除的记录数。</returns>
        public int DeleteEntities<T>(string conditions, params object[] parameters)
        {
            TableMap tableMap = GetTableMap(typeof(T).FullName);
            return GetDatabase().ExecuteNonQuery(tableMap.DeleteByConditionsCommand + conditions, parameters);
        }

        /// <summary>
        /// 插入记录。
        /// <br/>异常：SqlException(插入数据违反数据约束，映射中指定的SQL命令不合法。)
        /// </summary>
        /// <param name="t">要插入到数据库中的实体。</param>
        /// <returns>实际插入的记录数。</returns>
        public int InsertEntity<T>(T t) where T : IEntity, new()
        {
            Type entityType = typeof(T);
            TableMap tableMap = GetTableMap(entityType.FullName);
            int result = 0;
            using (DbCommand cmd = GetDatabase().GetStoredProcCommand(tableMap.InsertCommand))
            {
                int i = 0;
                int j = 0;
                object propValue;
                foreach (ColumnMap cm in tableMap.ColumnMaps)
                {
                    if (cm.IsInsert)
                    {
                        try
                        {
                            propValue = entityType.GetProperty(cm.PropertyName, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(t, null);
                        }
                        catch (AmbiguousMatchException e)
                        {
                            throw new OrMapException("实体" + entityType.FullName + "中没有找到指定的属性：" + cm.PropertyName, e);
                        }

                        //为要Insert的字段生成参数
                        cmd.Parameters.Add(CreateSqliteParameter(cm.ColumnName));

                        if (propValue == null)
                            cmd.Parameters[j].Value = DBNull.Value;
                        else
                            cmd.Parameters[j].Value = propValue;
                        j++;
                    }
                }

                Database database = GetDatabase();
                DbConnection connection = database.GetNewOpenConnection();
                cmd.Connection = connection;
                result = cmd.ExecuteNonQuery();
                int identity = int.Parse(new SQLiteCommand("SELECT LAST_INSERT_ROWID()", (SQLiteConnection)connection).ExecuteScalar().ToString());
                connection.Close();

                foreach (ColumnMap cm in tableMap.ColumnMaps)
                {
                    if (!cm.IsInsert)
                    {
                        try
                        {
                            entityType.GetProperty(cm.PropertyName, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).SetValue(t, identity, null);
                            break;
                        }
                        catch (AmbiguousMatchException e)
                        {
                            throw new OrMapException("实体" + entityType.FullName + "中没有找到指定的属性：" + cm.PropertyName, e);
                        }
                    }
                }
                t.IsNew = false;
            } // end of using...
            return result;
        }

        /// <summary>
        /// 修改记录，可能返回0。
        /// <br/>异常：OrMapException(没有提供有效的修改命令)，SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="t">要修改的实体。</param>
        /// <param name="keys">要修改实体的主键。</param>
        /// <returns>实际修改的记录数。</returns>
        public int UpdateEntity<T>(T t, params object[] keys) where T : IEntity, new()
        {
            Type entityType = typeof(T);
            TableMap tableMap = GetTableMap(entityType.FullName);
            if (string.IsNullOrEmpty(tableMap.UpdateCommand))
            {
                throw new Exception("在OR映射中没有指定有效的记录更新命令。");
            }

            using (DbCommand cmd = GetDatabase().GetStoredProcCommand(tableMap.UpdateCommand, keys))
            {
                int j = 0;
                foreach (ColumnMap cm in tableMap.ColumnMaps)
                {
                    if (cm.IsUpdate)
                    {
                        object propValue;
                        try
                        {
                            propValue = entityType.GetProperty(cm.PropertyName, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(t, null);
                        }
                        catch (AmbiguousMatchException e)
                        {
                            throw new OrMapException("实体" + entityType.FullName + "中没有找到指定的属性：" + cm.PropertyName, e);
                        }
                        //为要更新的字段生成Parameter
                        cmd.Parameters.Add(CreateSqliteParameter(cm.ColumnName));
                        if (propValue == null)
                            cmd.Parameters[j].Value = DBNull.Value;
                        else
                            cmd.Parameters[j].Value = propValue;
                        j++;
                    }
                }

                int i = 0;
                foreach (ColumnMap cm in tableMap.PrimaryKeyMaps)
                {
                    cmd.Parameters.Add(CreateSqliteParameter(cm.ColumnName));
                    cmd.Parameters[j].Value = keys[i];
                    i++;
                    j++;
                }

                return GetDatabase().ExecuteNonQuery(cmd);
            } // end of using...
        }

        private SQLiteParameter CreateSqliteParameter(string columnName)
        {
            return new SQLiteParameter("@" + columnName);
        }

        /// <summary>
        /// 按条件查找实体列表。
        /// <br/>异常：SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="condition">带WHERE的条件字符串。</param>
        /// <returns>实体列表。</returns>
        public IList<T> SearchEntities<T>(string condition) where T : IEntity, new()
        {
            Type entityType = typeof(T);
            TableMap tableMap = GetTableMap(entityType.FullName);

            IList<T> entities = new List<T>();

            using (IDataReader reader = GetDatabase().ExecuteReader(tableMap.SearchCommand + condition))
            {
                while (reader.Read())
                {
                    T t = new T();
                    LoadFromReader(t, reader);
                    entities.Add(t);
                }
            } // end of using ...
            return entities;
        }

        /// <summary>
        /// 按条件查找实体列表。
        /// <br/>异常：SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="condition">带WHERE的条件字符串。</param>
        /// <returns>实体列表。</returns>
        public IList<T> SearchEntities<T>(string condition, params object[] parameters) where T : IEntity, new()
        {
            Type entityType = typeof(T);
            TableMap tableMap = GetTableMap(entityType.FullName);

            IList<T> entities = new List<T>();

            string sqlText = string.Format("select * from {0} {1}", tableMap.TableName, condition);
            using (IDataReader reader = GetDatabase().ExecuteReader(sqlText, parameters))
            {
                while (reader.Read())
                {
                    T t = new T();
                    LoadFromReader(t, reader);
                    entities.Add(t);
                }
            } // end of using ...
            return entities;
        }
        #endregion

        /// <summary>
        /// 按条件查找实体，允许返回null。
        /// <br/>异常：SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="condition">带WHERE的条件字符串。</param>
        /// <returns>查到的第一个实体。</returns>
        public T FindEntity<T>(string condition) where T : IEntity, new()
        {
            TableMap tableMap = GetTableMap(typeof(T).FullName);
            using (IDataReader reader = GetDatabase().ExecuteReader(tableMap.SearchCommand + condition))
            {
                while (reader.Read())
                {
                    T t = new T();
                    LoadFromReader(t, reader);
                    return t;
                }
            } // end of using ...
            return default(T);
        }

        /// <summary>
        /// 按条件查找实体，允许返回null。
        /// <br/>异常：SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="condition">带WHERE的条件字符串。</param>
        /// <param name="parameters">占位符对应参数列</param>
        /// <returns>查到的第一个实体。</returns>
        public T FindEntity<T>(string condition, params object[] parameters) where T : IEntity, new()
        {
            TableMap tableMap = GetTableMap(typeof(T).FullName);
            using (IDataReader reader = GetDatabase().ExecuteReader(string.Format("select * from {0} {1}", tableMap.TableName, condition), parameters))
            {
                while (reader.Read())
                {
                    T t = new T();
                    LoadFromReader(t, reader);
                    return t;
                }
            } // end of using ...
            return default(T);
        }

        /// <summary>
        /// 根据主键查找实体，允许返回null。
        /// <br/>异常：SqlException(命令执行出错，条件字符串格式错误)
        /// </summary>
        /// <param name="keys">主键。</param>
        /// <returns>实体。</returns>
        public T FetechEntity<T>(params object[] keys) where T : IEntity, new()
        {
            TableMap tableMap = GetTableMap(typeof(T).FullName);

            if (string.IsNullOrEmpty(tableMap.FetchCommand))
            {
                throw new OrMapException("在OR映射中没有指定有效的FetchCommand");
            }

            using (IDataReader reader = GetDatabase().ExecuteReader(tableMap.FetchCommand, keys))
            {

                T t = new T();
                if (reader.Read())
                {
                    LoadFromReader(t, reader);
                    return t;
                }
                return default(T);
            } // end of using ...
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public string[] GetKeyPropertyNames<T>() where T : IEntity, new()
        {
            TableMap tableMap = GetTableMap(typeof(T).FullName);
            IList<ColumnMap> list = tableMap.PrimaryKeyMaps;
            string[] names = new string[list.Count];

            Type t = typeof(T);
            for (int i = 0; i < list.Count; i++)
            {
                names[i] = list[i].PropertyName;
            }
            return names;
        }

        /// <summary>
        /// 读取数据库中一条记录的内容到实体中。
        /// </summary>
        /// <param name="t">存放读取出来的数据的实体。</param>
        /// <param name="reader">指向一条记录。</param>
        public void LoadFromReader<T>(T t, IDataReader reader) where T : IEntity, new()
        {
            int i;
            Type entityType = typeof(T);
            TableMap tableMap = GetTableMap(entityType.FullName);
            PropertyInfo propInfo;
            if (reader != null && !reader.IsClosed)
            {
                foreach (ColumnMap cm in tableMap.ColumnMaps)
                {
                    try
                    {
                        propInfo = entityType.GetProperty(cm.PropertyName, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
                    }
                    catch (AmbiguousMatchException e)
                    {
                        throw new OrMapException("实体" + entityType.FullName + "中没有找到指定的属性：" + cm.PropertyName, e);
                    }

                    i = reader.GetOrdinal(cm.ColumnName);
                    if (!reader.IsDBNull(i))
                    {
                        object columnValue = reader.GetValue(i);

                        Type propType = propInfo.PropertyType;

                        if (propType.FullName.StartsWith("System.Nullable"))
                        {
                            if (propType.GetGenericArguments()[0].IsEnum && columnValue is string)
                            {
                                propInfo.SetValue(t, Enum.Parse(propType.GetGenericArguments()[0], columnValue.ToString()), null);
                            }
                            else
                            {
                                propInfo.SetValue(t, columnValue, null);
                            }
                        }
                        else
                        {
                            if (propType.IsEnum && columnValue is string)
                            {
                                propInfo.SetValue(t, Enum.Parse(propType, columnValue.ToString()), null);
                            }
                            else
                            {
                                propInfo.SetValue(t, columnValue, null);
                            }
                        }
                    }
                    else  // is a dbnull value
                    {
                        propInfo.SetValue(t, null, null);
                    }
                }
            }
            t.IsNew = false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        [Obsolete]
        public int ExecuteNonQuery(string commandText, DbParameter[] parameters)
        {
            Database db = GetDatabase();
            using (DbCommand cmd = db.GetSqlStringCommand(commandText))
            {
                if (parameters != null)
                {
                    foreach (object value in parameters)
                    {
                        cmd.Parameters.Add(value);
                    }
                }
                return db.ExecuteNonQuery(cmd);
            } // end of using ...

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        [Obsolete]
        public object ExecuteScalar(string commandText, DbParameter[] parameters)
        {
            Database db = GetDatabase();
            using (DbCommand cmd = db.GetSqlStringCommand(commandText))
            {
                if (parameters != null)
                {
                    foreach (object value in parameters)
                    {
                        cmd.Parameters.Add(value);
                    }
                }
                return db.ExecuteScalar(cmd);
            } // end of using ...
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        [Obsolete]
        public IDataReader ExecuteReader(string commandText, DbParameter[] parameters)
        {
            Database db = GetDatabase();
            using (DbCommand cmd = db.GetSqlStringCommand(commandText))
            {
                if (parameters != null)
                {
                    foreach (object value in parameters)
                    {
                        cmd.Parameters.Add(value);
                    }
                }
                return db.ExecuteReader(cmd);
            } // end of using ...

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
        {
            return GetDatabase().ExecuteScalar(storedProcedureName, parameterValues);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues)
        {
            return GetDatabase().ExecuteReader(storedProcedureName, parameterValues);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
        {
            return GetDatabase().ExecuteNonQuery(storedProcedureName, parameterValues);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        /// 
        [Obsolete]
        public System.Dynamic.ExpandoObject FetchObject(string commandText, DbParameter[] parameters)
        {
            Database db = GetDatabase();
            using (DbCommand cmd = db.GetSqlStringCommand(commandText))
            {
                if (parameters != null)
                {
                    foreach (object value in parameters)
                    {
                        cmd.Parameters.Add(value);
                    }
                }
                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    if (reader.Read()) return db.LoadObject(reader);
                }
            } // end of using ...
            return null;
        }

        public System.Dynamic.ExpandoObject FetchObject(string storedProcedureName, params object[] parameterValues)
        {
            return GetDatabase().FetchObject(storedProcedureName, parameterValues);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        /// 
        [Obsolete]
        public IList<System.Dynamic.ExpandoObject> FetchObjectList(string commandText, DbParameter[] parameters)
        {
            Database db = GetDatabase();
            using (DbCommand cmd = db.GetSqlStringCommand(commandText))
            {
                if (parameters != null)
                {
                    foreach (object value in parameters)
                    {
                        cmd.Parameters.Add(value);
                    }
                }
                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    return db.LoadObjectList(reader);
                }
            } // end of using ...
        }

        public IList<System.Dynamic.ExpandoObject> FetchObjectList(string storedProcedureName, params object[] parameterValues)
        {
            return GetDatabase().FetchObjectList(storedProcedureName, parameterValues);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parametersText"></param>
        /// <param name="bodyText"></param>
        public void CreateProcedure(string procedureName, string parametersText, string bodyText)
        {
            GetDatabase().CreateProcedure(procedureName, parametersText, bodyText, isDebug);
        }
    }
}
